<?php

class Quotation_Model extends Model
{
	public function __construct()
	{
		parent::Model();
		
		// open the database
		$this->load->database();
	}
	
	function add_record($date, $requester_name, $email, $origin_address_id, $destination_address_id,
										$pickup_date, $goods_description, $other_comment, $shipping_desctination_comment, 
										$handle_instruction, $designation_id, $pickup_time, $dock_closing_time, $total_weight,
										$is_hazardous, $your_number, $total_pieces)
	{
		$sql = "INSERT INTO quotation (date, requester_name, email, origin_address_id, destination_address_id,
										pickup_date, goods_description, other_comment, shipping_desctination_comment, 
										handling_instruction, designation_id, pickup_time, dock_closing_time, total_weight,
										is_hazardous, phone, pieces)
				VALUES (".$this->db->escape($date).", ".$this->db->escape($requester_name).", ".$this->db->escape($email).
						", ".$this->db->escape($origin_address_id).", ".$this->db->escape($destination_address_id).", ".$this->db->escape($pickup_date).
						", ".$this->db->escape($goods_description).", ".$this->db->escape($other_comment).", ".$this->db->escape($shipping_desctination_comment).
						", ".$this->db->escape($handle_instruction).", ".$this->db->escape($designation_id).", ".$this->db->escape($pickup_time).				
						", ".$this->db->escape($dock_closing_time).", ".$this->db->escape($total_weight).", ".$this->db->escape($is_hazardous).
						", ".$this->db->escape($your_number).", ".$this->db->escape($total_pieces).")";

		$this->db->query($sql);
	}
	
	function add_address($contact_name, $company_name, $street, $p_code, $city, $phone_number, $fax, $email)
	{
		$sql = "INSERT INTO address (name, company_name, street, postal_code, 
										city_id, phone, fax, email)
				VALUES (".$this->db->escape($contact_name).", ".$this->db->escape($company_name).
						", ".$this->db->escape($street).", ".$this->db->escape($p_code).
						", ".$this->db->escape($this->find_city_id($city)).
						", ".$this->db->escape($phone_number).", ".$this->db->escape($fax).
						", ".$this->db->escape($email).")";

		$this->db->query($sql);
		return mysql_insert_id();
	}
	
	function find_city_id($location)
	{
		$id=-1;
		
		//tokenize
		$city = strtok($location, ",");
		$state_province = strtok(",");
		$country = strtok(",");

		$city=trim($city);
		$state_province=trim($state_province);
		$country=trim($country);
		
		echo "location = ".$country." | ".$state_province." | ".$city;

		$province_id=$this->find_province_id($state_province);
		
		$result = $this->db->query('SELECT city_id
							FROM city 
							WHERE city_name like \'%'.$city.'%\' AND state_province_id = \''.$province_id.'\'');
		
		if($result->num_rows()>0)
		{	foreach($result->result() as $row)
			{
				$id=$row->city_id;
			}
		}
		return $id;
	}
	
	function find_province_id($province)
	{
		$id=-1;
		
		$result = $this->db->query('SELECT state_province_id
							FROM state_province 
							WHERE state_province_name like \'%'.$province.'%\'');
							
		foreach($result->result() as $row)
		{
			$id=$row->state_province_id;
		}

		return $id;
	}

	public function get_province_list()
	{
		$is_delete=0;
		$result = $this->db->query('SELECT state_province_name
							FROM state_province
							WHERE is_deleted = \''.$is_delete.'\'');

		$province_state_list=array();
		$province_state_list[0]="";
		$i=1;
		
		foreach($result->result() as $row)
		{
			$province_state_list[$i] = $row->state_province_name;
			$i++;
		}
		
		return $province_state_list;
	}
	
	public function get_city_list()
	{
		$is_delete=0;
		$result = $this->db->query('SELECT city_name, state_province_id
							FROM city
							WHERE is_deleted = \''.$is_delete.'\'
							ORDER BY city_name');
		
		$city_list=array();
		$city_list[0]="";

		$i=1;
		
		foreach($result->result() as $row)
		{
			$sql = $this->db->query('SELECT state_province_name, country_name
							FROM state_province
							WHERE state_province_id = \''.$row->state_province_id.'\'');

			foreach($sql->result() as $row1)
			{
				$city_list[$i] = $row->city_name.", ".$row1->state_province_name.", ".$row1->country_name;
			}
							
			$i++;
		}
		
		return $city_list;
	}

}
?>